--대분류 카테고리 테이블
create table BigClass(
bigClassNo number primary key,
bigClassname varchar2(20) not null
);
--소분류 카테고리 테이블
create table smallClass(
smallClassNo number primary key,
smallClassname varchar2(20) not null,
bigClassNo number not null
--,constraint fk_BigClass_smallClass foreign key(bigClassNo) references BigClass(bigClassNo)
);
--회원번호 시퀀스 생성
create sequence projectmember_seq;
--회원 테이블 
create table projectmember(
memberNo number primary key,
memberId varchar2(30) not null unique,
memberPassword varchar2(30) not null,
memberEmail varchar2(100) not null,
memberAddress varchar2(100) not null,
memberName varchar2(30) not null,
memberTel varchar2(20) not null,
grade number default 2,
memberActChk number not null
);
--경매등록 시퀀스 생성
create sequence auctionRegister_seq;
--경매등록테이블
create table auctionRegister(
 boardNo number primary key,
 boardTitle varchar2(100) not null,
 time_posted date not null,
 boardContent varchar2(1200) not null,
 hits number not null,
 auctionPrice number not null,
 auctionTime varchar2(20) not null,
 quantity number not null,
 buyPrice number not null,
 attatchedFile varchar2(50),
 activeCheck number not null,
 inactiveReason varchar2(30),
 smallClassNo number not null,
 memberNo number not null
-- ,constraint fk_smallClass_auction foreign key(smallClassNo) references smallClass(smallClassNo),
-- constraint fk_projectmember_auction foreign key(memberNo) references projectmember(memberNo)
 );
--auctionRegister 컬럼명
--boardNo, boardTitle, time_posted, boardContent, hits, auctionPrice, auctionTime, 
--quantity, buyPrice, attatchedFile, activeCheck, inactiveReason, smallClassNo, memberNo


--관리자테이블
create table admin(
	adminid varchar2(20) primary key,
	adminpassword varchar2(20) not null
);

--공지사항 시퀀스 생성
create sequence notice_seq;
--공지사항테이블
create table notice(
 noticeNo number primary key,
 noticeTitle varchar2(20) not null,
 noticeContent varchar2(4000) not null,
 time_posted date not null,
 hits number not null ,
 adminid varchar2(20) not null
-- ,constraint fk_admin_notice foreign key(adminid) references admin(adminid)
);

 --쪽지함 시퀀스
create sequence messageNo_seq;
--쪽지함 테이블 
 create table message(
 messageNo number primary key,
 memberNo number not null,
 messageTitle varchar2(50) not null,
 messageContent varchar2(600) not null,
 sendTime date,
 readCheck number not null
 --,constraint pk_projectmember_message foreign key(memberNo) references projectmember(memberNo)
 );
-- 찜목록 시퀀스
 create sequence  wishListNo_seq;
--찜목록 테이블
drop table wishList
 create table wishList(
 wishListNo number primary key,
 memberNo number not null,
 boardNo number not null,
 boardTitle varchar2(50)
 --,constraint pk_projectmember_wishList foreign key(memberNo) references projectmember(memberNo)
 );
 
 insert into wishList  (wishListNo, memberNo, boardNo, boardTitle) values(wishListNo_seq.nextval, 1,1,'게시물 제목 들어와1');
 insert into wishList  (wishListNo, memberNo, boardNo, boardTitle) values(wishListNo_seq.nextval, 1,1,'게시물 제목 들어와2');
 insert into wishList  (wishListNo, memberNo, boardNo, boardTitle) values(wishListNo_seq.nextval, 1,1,'게시물 제목 들어와3');

 --회원 상품구매내역 시퀀스
 create sequence purchaseHistory_seq; 
--회원 상품구매내역 테이블
 create table purchaseHistory(
 purchaseHistoryNo number primary key,
 memberNo number,
 boardNo number not null,
 boardTitle varchar2(20) not null,
 purchasePrice number not null,
 quantity number not null
 --,constraint pk_projectmember_History foreign key(memberNo) references projectmember(memberNo)
 ) ;
--회원상품입찰내역 시퀀스
 create sequence bidHistory_seq; 
 --회원 상품입찰내역 테이블
 drop table bidHistory
 create table bidHistory(
 bidHistoryNo number primary key,
 memberNo number not null,
 auctionPrice number not null,
 quantity number not null,
 boardNo number not null,
 boardTitle varchar2(20) not null
--constraint pk_projectmember_bidHistory foreign key(memberNo) references projectmember(memberNo)
 );
--경매물품 입찰 내역 시퀀스
 create sequence bid_seq;
 --경매물품 입찰 내역 (게시물에 표시되는 경매내역목록)
 drop table bid;
 create table bid(
 bidNo number primary key,
 boardNo number not null,
 boardTitle varchar2(40) not null,
 bidPrice number not null,
 sellerId varchar2(20)  not null,
 bidTime date not null, 
 memberNo number not null
 --,constraint pk_projectmember_bid foreign key(memberNo) references projectmember(memberNo),
 --constraint pk_auctionRegister_bid foreign key(boardNo) references auctionRegister(boardNo)
 );
  --삭제승인요청테이블
 create table Deletecomfirm(
 	DeleteComfrimNo number primary key,
  	memberId varchar2(20) not null,
 	requestReason varchar2(20) not null,
 	requestTime date not null,
 	boardNo number not null
 	--,constraint pk_auctionRegister_comfirm foreign key(boardNo) references auctionRegister(boardNo)
 );
 	select count(*) from bidHistory where memberNo=1
 --insert 모음
 

 insert into bid(bidNo,boardNo,boardTitle,bidPrice,sellerId,bidTime,memberNo) values(bid_seq.nextval,2,'게시물제목',100,'b',sysdate,4);
insert into bid(bidNo,boardNo,boardTitle,bidPrice,sellerId,bidTime,memberNo) values(bid_seq.nextval,2,'게시물제목',100,'b',sysdate,8);
insert into bid(bidNo,boardNo,boardTitle,bidPrice,sellerId,bidTime,memberNo) values(bid_seq.nextval,2,'게시물제목',100,'b',sysdate,8);

 
 
 
 
 
 insert into bidhistory  (bidHistoryNo, memberNo, auctionPrice, quantity, boardNo , boardTitle) values(bidHistory_seq.nextval, 1,4000,3,1,'2223하하하');
 insert into bidhistory  (bidHistoryNo, memberNo, auctionPrice, quantity, boardNo , boardTitle) values(bidHistory_seq.nextval, 1,4000,3,1,'들어가');
 insert into bidhistory  (bidHistoryNo, memberNo, auctionPrice, quantity, boardNo , boardTitle) values(bidHistory_seq.nextval, 1,4000,3,1,'입찰내역2');
 insert into bidhistory  (bidHistoryNo, memberNo, auctionPrice, quantity, boardNo , boardTitle) values(bidHistory_seq.nextval, 1,4000,3,1,'이건 안나와야돼');

 
insert into notice ( noticeNo, noticeTitle, noticeContent, time_posted, hits,adminid) 
values (notice_seq.nextval,'1번공지사항','이러이러하다라는 내용',sysdate,0,'admin');
insert into notice ( noticeNo, noticeTitle, noticeContent, time_posted, hits,adminid) 
values (notice_seq.nextval,'2번공지사항','이하다라는 내용',sysdate,0,'admin');

insert into notice ( noticeNo, noticeTitle, noticeContent, time_posted, hits,adminid) 
values (notice_seq.nextval,'3번공지사항','이하다333라는 내용',sysdate,0,'admin');
 
insert into notice ( noticeNo, noticeTitle, noticeContent, time_posted, hits,adminid) 
values (notice_seq.nextval,'4번공지사항','이하54444다라는 내용',sysdate,0,'admin');
 
insert into notice ( noticeNo, noticeTitle, noticeContent, time_posted, hits,adminid) 
values (notice_seq.nextval,'5번공지사항','이하45다라는 내용',sysdate,0,'admin');
 
insert into notice ( noticeNo, noticeTitle, noticeContent, time_posted, hits,adminid) 
values (notice_seq.nextval,'6번공지사항','이하6656다라는 내용',sysdate,0,'admin');

 
insert into admin (adminid,adminpassword) values('admin','1234');


insert into auctionRegister(
boardNo, boardTitle, time_posted, boardContent, hits, auctionPrice, auctionTime, 
quantity, buyPrice, attatchedFile, activeCheck, inactiveReason, smallClassNo, memberNo
)
values(auctionRegister_seq.nextval,'청소기삽니다',sysdate,'삼성청소기',
	  0,50000,12,1,30000,'첨부파일경로',0,'낙찰',7,1);

insert into auctionRegister(
boardNo, boardTitle, time_posted, boardContent, hits, auctionPrice, auctionTime, 
quantity, buyPrice, attatchedFile, activeCheck, inactiveReason, smallClassNo, memberNo
)
values(auctionRegister_seq.nextval,'LG 청소기삽니다',sysdate,'LG청소기',
	  0,50000,12,1,30000,'첨부파일경로',0,'낙찰',7,1);

insert into auctionRegister(
boardNo, boardTitle, time_posted, boardContent, hits, auctionPrice, auctionTime, 
quantity, buyPrice, attatchedFile, activeCheck, inactiveReason, smallClassNo, memberNo
)
values(auctionRegister_seq.nextval,'낫소라켓삽니다',sysdate,'낫소라켓',
	  0,50000,12,1,30000,'첨부파일경로',0,'낙찰',8,2);


insert into auctionRegister(
boardNo, boardTitle, time_posted, boardContent, hits, auctionPrice, auctionTime, 
quantity, buyPrice, attatchedFile, activeCheck, inactiveReason, smallClassNo, memberNo
)
values(auctionRegister_seq.nextval,'삽산다',sysdate,'삽',
	  0,50000,12,1,30000,'첨부파일경로',0,'낙찰',5,2);
select * from projectmember
insert into projectmember
( memberNo, memberId, memberPassword, memberEmail, memberAddress, memberName, memberTel, memberActChk)
values (projectmember_seq.nextval,'java','1234','java@gmail.com','판교','영심이','010-1234-0101',0);
insert into projectmember
( memberNo, memberId, memberPassword, memberEmail, memberAddress, memberName, memberTel, memberActChk)
values (projectmember_seq.nextval,'spring','1234','spring@gmail.com','신림','일심이','010-1234-0112',0);
insert into projectmember
( memberNo, memberId, memberPassword, memberEmail, memberAddress, memberName, memberTel, memberActChk)
values (projectmember_seq.nextval,'a','1','a@gmail.com','강남','영심이','010-1234-0101',0);
insert into projectmember
( memberNo, memberId, memberPassword, memberEmail, memberAddress, memberName, memberTel, memberActChk)
values (projectmember_seq.nextval,'b','1','b@gmail.com','판교','일심이','010-1234-0112',0);
insert into projectmember
( memberNo, memberId, memberPassword, memberEmail, memberAddress, memberName, memberTel, memberActChk)
values (projectmember_seq.nextval,'c','1','c@gmail.com','강남','영심이','010-1234-0101',0);
insert into projectmember
( memberNo, memberId, memberPassword, memberEmail, memberAddress, memberName, memberTel, memberActChk)
values (projectmember_seq.nextval,'d','1','d@gmail.com','신림','일심이','010-1234-0112',0);
insert into projectmember
( memberNo, memberId, memberPassword, memberEmail, memberAddress, memberName, memberTel, memberActChk)
values (projectmember_seq.nextval,'abdc','1','adbc@gmail.com','신림','일심이','010-1234-0112',0);


insert into smallClass (smallClassNo, smallClassname, bigClassNo) values(1,'컴퓨터',1);
insert into smallClass (smallClassNo, smallClassname, bigClassNo) values(2,'mp3',1);
insert into smallClass (smallClassNo, smallClassname, bigClassNo) values(3,'빔프로젝터',1);
insert into smallClass (smallClassNo, smallClassname, bigClassNo) values(7,'청소기',1);

insert into smallClass (smallClassNo, smallClassname, bigClassNo) values(4,'텐트',2);
insert into smallClass (smallClassNo, smallClassname, bigClassNo) values(5,'삽',2);
insert into smallClass (smallClassNo, smallClassname, bigClassNo) values(6,'서핑보드',2);
insert into smallClass (smallClassNo, smallClassname, bigClassNo) values(8,'낫소라켓',2);

 
insert into BigClass (bigClassNo, bigClassname) values(1,'가전');
insert into BigClass (bigClassNo, bigClassname) values(2,'레저');

 --------------drop
 
drop table bid;
drop table bidHistory;
drop table purchaseHistory;
drop table wishList;
drop table message;

drop table notice;
drop table Deletecomfirm;
drop table auctionRegister;
drop table smallClass;
drop table BigClass;
drop table notice;
drop table admin;
drop table projectmember;

drop sequence projectmember_seq;
drop sequence auctionRegister_seq;
drop notice_seq;
commit

